import pandas as pd
import schedule
import time
import mysql.connector
from datetime import datetime, timedelta
from bank_info import BankInfoExtractor
from db_tools import get_mysql_connection

# "0": "\u0030\ufe0f\u20e3",

num_emoji_map_ori = {
    "0": "\u0030\ufe0f\u20e3",
    "1": "",
    "2": "2️⃣",
    "3": "3️⃣",
    "4": "4️⃣",
    "5": "5️⃣",
    "6": "6️⃣",
    "7": "7️⃣",
    "8": "8️⃣",
    "9": "9️⃣",
}
num_emoji_map = {
    "0": "\u0030\ufe0f\u20e3",
    "1": "\u0031\ufe0f\u20e3",
    "2": "\u0032\ufe0f\u20e3",
    "3": "\u0033\ufe0f\u20e3",
    "4": "\u0034\ufe0f\u20e3",
    "5": "\u0035\ufe0f\u20e3",
    "6": "\u0036\ufe0f\u20e3",
    "7": "\u0037\ufe0f\u20e3",
    "8": "\u0038\ufe0f\u20e3",
    "9": "\u0039\ufe0f\u20e3",
}
num_emoji_map_circle = {
    "0": "⓪",
    "1": "①",
    "2": "②",
    "3": "③",
    "4": "④",
    "5": "⑤",
    "6": "⑥",
    "7": "⑦",
    "8": "⑧",
    "9": "⑨",
}


def num_to_emoji(num):
    num_str = str(num).zfill(2)  # 补齐两位
    return "".join(num_emoji_map[d] for d in num_str)


class BankDataProcessor:
    def __init__(self, api_key=""):
        self.extractor = BankInfoExtractor(api_key)
        self.last_processed_time = None

    def get_time_window(
        self,
    ):
        current_time = datetime.now()

        # 计算上一个5分钟时间窗口
        current_minute = current_time.minute
        window_start_minute = (current_minute // 5) * 5 - 5

        if window_start_minute < 0:
            # 跨小时处理
            window_start = current_time.replace(
                hour=current_time.hour - 1,
                minute=window_start_minute + 60,
                second=0,
                microsecond=0,
            )
        else:
            window_start = current_time.replace(
                minute=window_start_minute, second=0, microsecond=0
            )

        window_end = window_start + timedelta(minutes=5) - timedelta(microseconds=1)
        return window_start, window_end

    def process_time_window_inquiries(self, window_start, window_end):
        """处理指定时间窗口的客户询问数据"""

        print(f"处理时间窗口: {window_start} - {window_end}")

        conn = get_mysql_connection()
        if not conn:
            return

        try:
            # 查询指定时间窗口的数据，排除已处理的记录
            query = """
            SELECT ci.id, nvl(ci.serial_number, ci.id) as serial_number
            , ci.content, ci.ask_time
            , ci.wechat_id, ci.wechat_name, ci.group_name, ci.group_id 
            FROM (select * from 
                    (select *, row_number() over (partition by content order by created desc) as rnk
                    from customer_inquiries
                    WHERE ask_time >= %s
                    AND ask_time < %s)a 
                where rnk=1) ci
            LEFT JOIN processed_financial_inquiries pfi ON ci.id = pfi.serial_number
            where pfi.serial_number IS NULL
            ORDER BY ci.ask_time
            """
            cursor = conn.cursor(dictionary=True)
            cursor.execute(query, (window_start, window_end))
            inquiries = cursor.fetchall()
            df = pd.DataFrame(inquiries)
            print(df.shape)
            df_dp = df.drop_duplicates(["content"], keep="last")
            print(df_dp.shape)

            if not inquiries:
                print(f"时间窗口内无新的客户询问记录")
                return

            print(f"找到 {len(inquiries)} 条待处理记录")
            print(f"去重后剩余{df_dp.shape[0]}条")

            # 批量处理数据
            processed_data = []

            for index, inquiry in df_dp.iterrows():
                try:
                    current_date = (
                        inquiry["ask_time"].strftime("%Y-%m-%d")
                        if inquiry["ask_time"]
                        else None
                    )
                    print(index, inquiry["content"])
                    extracted_info = self.extractor.extract_info(
                        text=inquiry["content"], current_date=current_date
                    )

                    processed_data.append((inquiry, extracted_info))

                except Exception as e:
                    print(f"✗ 记录 ID: {inquiry['id']} 处理出错: {str(e)}")
                    continue

            # 批量插入处理后的数据
            if processed_data:
                self._batch_insert_processed_data(conn, processed_data)
                print(f"批量插入完成，共 {len(processed_data)} 条记录")

            self.last_processed_time = None

        except Exception as e:
            print(f"处理时间窗口数据时出错: {str(e)}")
        finally:
            conn.close()

    def _is_valid_extraction(self, extracted_info):
        """检查提取的信息是否有效"""
        if not extracted_info:
            return False
        required_fields = ["bank", "biz_type", "amount"]
        return any(extracted_info.get(field) for field in required_fields)

    def _batch_insert_processed_data(self, conn, processed_data):
        """批量插入处理后的数据"""
        insert_query = """
        INSERT INTO processed_financial_inquiries 
        (serial_number, content, ask_time, part_order, bank, biz_type, amount, 
         start_date, end_date, days, price, bonus_type, total_amt, remark)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        batch_values = []
        for inquiry, extracted_info in processed_data:
            for item_order, item in enumerate(extracted_info):
                start_date = self._parse_date(item.get("start_date"))
                end_date = self._parse_date(item.get("end_date"))

                values = (
                    inquiry["serial_number"],
                    inquiry["content"],
                    inquiry["ask_time"].to_pydatetime(),
                    item_order,
                    item.get("bank"),
                    item.get("biz_type"),
                    item.get("amount"),
                    start_date,
                    end_date,
                    item.get("days"),
                    item.get("price"),
                    item.get("bonus_type"),
                    item.get("total_amt"),
                    item.get("remark"),
                )
                batch_values.append(values)

        try:
            cursor = conn.cursor()
            cursor.executemany(insert_query, batch_values)
            conn.commit()
            cursor.close()
        except Exception as e:
            print(f"批量插入数据时出错: {str(e)}")
            conn.rollback()
            raise

    def _batch_insert_msg_data(self, conn, processed_data):
        """批量插入处理后的数据"""
        insert_query = """
        INSERT INTO send_msg 
        (chat_id, msg_info)
        VALUES (%s, %s)
        """

        batch_values = processed_data

        try:
            cursor = conn.cursor()
            cursor.executemany(insert_query, batch_values)
            conn.commit()
            cursor.close()
        except Exception as e:
            print(f"批量插入数据时出错: {str(e)}")
            conn.rollback()
            raise

    def _batch_insert_msg_data_history(self, conn, processed_data):
        """批量插入处理后的数据"""
        insert_query = """
        INSERT INTO send_msg_history 
        (chat_id, msg_info)
        VALUES (%s, %s)
        """

        batch_values = processed_data

        try:
            cursor = conn.cursor()
            cursor.executemany(insert_query, batch_values)
            conn.commit()
            cursor.close()
        except Exception as e:
            print(f"批量插入数据时出错: {str(e)}")
            conn.rollback()
            raise

    def _parse_date(self, date_str):
        """解析日期字符串"""
        if not date_str:
            return None

        try:
            date_formats = ["%Y-%m-%d", "%Y/%m/%d", "%m/%d/%Y", "%d/%m/%Y"]
            for fmt in date_formats:
                try:
                    return datetime.strptime(date_str, fmt).date()
                except ValueError:
                    continue
            return None
        except:
            return None

    def process_msg_info(self, window_start, window_end):
        print(f"处理时间窗口: {window_start} - {window_end}")

        conn = get_mysql_connection()
        if not conn:
            return
        # and serial_number in (
        # select id from customer_inquiries where group_id in (
        #     '45328185344@chatroom', '48158698401@chatroom', '47346199248@chatroom', '44025320011@chatroom', '49825886197@chatroom', '43355410028@chatroom', '38791386056@chatroom', '53136321690@chatroom', '54070913445@chatroom', '24828205213@chatroom', '55990876316@chatroom', '44865395390@chatroom', '57440000508@chatroom', '39212960781@chatroom', '50103365536@chatroom', '51765522223@chatroom', '21189259973@chatroom', '50010691235@chatroom', '52464220320@chatroom', '48334466723@chatroom', '21108057326@chatroom', '21116116572@chatroom', '46046979907@chatroom', '24980570651@chatroom', '20329261810@chatroom', '46142889655@chatroom', '39042485070@chatroom', '56287606483@chatroom', '39350090378@chatroom', '52300230625@chatroom', '25612751222@chatroom', '47475796942@chatroom', '24151399384@chatroom', '48400357000@chatroom', '47807964382@chatroom', '47746881228@chatroom', '52733722804@chatroom', '18455468369@chatroom', '39062888733@chatroom', '45485884731@chatroom', '20966042563@chatroom', '57871486008@chatroom', '20924247763@chatroom', '56614200900@chatroom', '53386828720@chatroom', '50440869344@chatroom', '34887976394@chatroom', '44978394328@chatroom', '39286185881@chatroom', '34448906737@chatroom', '55914083934@chatroom', '39147612025@chatroom', '48769864705@chatroom', '49040088182@chatroom'
        #     )
        # )
        try:
            # 查询指定时间窗口的数据，排除已处理的记录
            query = """
            select content
            ,count(distinct part_order) as part_num
            ,nvl(max(total_amt),0) as total_amt
            from processed_financial_inquiries
            where remark <> 'invalid'
            and ask_time >= %s
            and ask_time < %s
            and content not rlike '盈亏|包盈亏'
            group by content
            order by max(total_amt) desc
            """
            cursor = conn.cursor(dictionary=True)
            cursor.execute(query, (window_start, window_end))
            inquiries = cursor.fetchall()
            df = pd.DataFrame(inquiries)
            print(df.shape)
            df_dp = df.drop_duplicates(["content"], keep="last")
            print(df_dp.shape)

            if not inquiries:
                print(f"时间窗口内无新的客户询问记录")
                return

            print(f"找到 {len(inquiries)} 条待处理记录")
            print(f"去重后剩余{df_dp.shape[0]}条")

            processed_data = []
            msg_info_arr = []
            for index, inquiry in df_dp.iterrows():
                content = inquiry["content"]
                part_num = inquiry["part_num"]
                total_amt = inquiry["total_amt"]
                idx_str = num_to_emoji(index + 1)
                msg = f"{idx_str}-{total_amt}-{part_num}: {content}"
                msg_info_arr.append(msg)
            msg_info = "\n--------------\n".join(msg_info_arr)
            chat_id = "58414188306@chatroom----测试发单群"
            processed_data.append((chat_id, msg_info))

            # 批量插入处理后的数据
            if processed_data:
                self._batch_insert_msg_data(conn, processed_data)
                self._batch_insert_msg_data_history(conn, processed_data)
                print(f"批量插入完成，共 {len(processed_data)} 条记录")

            self.last_processed_time = None

        except Exception as e:
            print(f"处理时间窗口数据时出错: {str(e)}")
        finally:
            conn.close()

    def start_scheduler(self):
        """启动定时调度器"""
        print("=== 银行信息提取定时处理系统 ===")
        print("每5分钟整点执行数据处理任务")
        print("按 Ctrl+C 停止程序")
        print("=" * 40)

        # 定义一个包装函数来处理时间窗口
        def process_current_window():
            window_start, window_end = self.get_time_window()
            self.process_time_window_inquiries(window_start, window_end)
            self.process_msg_info(window_start, window_end)

        # 优雅地设置每5分钟整点执行
        for minute in range(0, 60, 5):
            schedule.every().hour.at(f":{minute:02d}").do(process_current_window)

        # 检查是否需要立即执行一次
        current_minute = datetime.now().minute
        if current_minute % 5 == 0:
            print("当前时间正好是5分钟整点，立即执行一次...")
            process_current_window()

        # 显示下次执行时间
        next_run = schedule.next_run()
        if next_run:
            print(f"下次执行时间: {next_run.strftime('%Y-%m-%d %H:%M:%S')}")

        # 持续运行调度器
        while True:
            try:
                schedule.run_pending()
                time.sleep(30)  # 每30秒检查一次
            except KeyboardInterrupt:
                print("\n收到中断信号，正在停止调度器...")
                break
            except Exception as e:
                print(f"调度器运行时出错: {str(e)}")
                time.sleep(60)

        print("调度器已停止")


# 使用示例
if __name__ == "__main__":
    processor = BankDataProcessor(api_key="your_api_key_here")
    # processor.process_time_window_inquiries(
    #     "2025-08-10 22:00:00", "2025-08-10 22:06:00"
    # )
    processor.process_msg_info("2025-08-30 16:15:00", "2025-08-30 16:19:59")
    processor.start_scheduler()
